// init_db.js
const sqlite3 = require('sqlite3').verbose();
// 连接数据库，注意路径和之前文件结构里的 database/andon.db 对应
const db = new sqlite3.Database('./database/andon.db'); 

db.serialize(() => {
  // 创建事件表
  db.run(`CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT CHECK(category IN ('quality', 'safety', 'other')), 
    severity INTEGER CHECK(severity BETWEEN 1 AND 5), 
    status TEXT DEFAULT 'reported' CHECK(status IN ('reported', 'in_progress', 'resolved', 'closed')), 
    reporter_id INTEGER NOT NULL,
    assigned_to INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // 创建用户表（简版）
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    role TEXT CHECK(role IN ('worker', 'supervisor', 'manager')) 
  )`);

  console.log('Database initialized');
  db.close(); 
});
